package DBLayer;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.*;

public class DBCustomer implements IFDBCustomer 
{
	 private  Connection connection;
	
	 public DBCustomer() 
	    {
	      connection = DbConnection.getInstance().getDBcon();
	    }
	 
	 public ArrayList<Customer> getAllCustomer() 
	 	{
	      return miscWhere("");
	    }
	 
		private ArrayList<Customer> miscWhere(String wClause)
		{
			ArrayList<Customer> customers = new ArrayList<>();
			
			ResultSet results;
			String query = buildQuery(wClause);
			
			try{
				Statement stmt = connection.createStatement();
				stmt.setQueryTimeout(5);
				results = stmt.executeQuery(query);
				
				
				while(results.next())
				{
					Customer customer = buildCustomer(results);
					customers.add(customer);
				}
				
				stmt.close();
				
			}
			catch(Exception ex){System.out.println(ex.toString());}
			return customers;
		}
	    
	 
	 private String buildQuery(String wClause)
		{
		    String query="SELECT customerId, name, surname, address, email, phone, company FROM CustomerList";
			
			if (wClause.length()>0)
				query=query+" WHERE "+ wClause;
				
			return query;
		}
		
	 private Customer buildCustomer(ResultSet results){
	        Customer customerObj = new Customer();
	          try{ 
	                customerObj.setCustomerId(results.getInt("customerId"));
	                customerObj.setCustomerName(results.getString("name"));
	                customerObj.setCustomerSurname(results.getString("surname"));
	                customerObj.setCustoemrAddress(results.getString("address"));
	                customerObj.setCustomerEmail(results.getString("email"));
	                customerObj.setCustomerPhone(results.getString("phone"));
	                customerObj.setCustomerCompany(results.getString("company"));
	        	  
	        
	          }
	         catch(Exception e)
	         {
	             System.out.println("Cannot create customer object.");
	         }
	         return customerObj;
	      
	    }
	 
	 public int updateCustomer(Customer customer)
		{
			Customer custObj  = customer;
			int rc=-1;

			String query="UPDATE CustomerList SET "+
			 	  "customerId ="+ custObj.getCustomerId()+", "+
			 	  "name ='"+ custObj.getCustomerName() + "', " +
	                          "surname ='"+ custObj.getCustomerSurname() + "', " +
	                          "address ='"+ custObj.getCustomerAddress() + "', " +
	                          "email ='"+ custObj.getCustomerEmail() + "' ," +
	                          "phone ='"+ custObj.getCustomerPhone() + "' ," +
	                          "company ='"+ custObj.getCustomerCompany() + "' " +
			          " WHERE customerId = "+ custObj.getCustomerId();
	                System.out.println("Update query:" + query);
	  		try{ 
		 		Statement stmt = connection.createStatement();
		 		stmt.setQueryTimeout(5);
		 	 	rc = stmt.executeUpdate(query);

		 	 	stmt.close();
			}
		 	catch(Exception ex){
		 	 	System.out.println("Update exception in customer db: "+ex);
		  	}
			return(rc);
		}
	 
	 
	 public int deleteCustomer(int customerId)
		{
	               int rc=-1;
		  
		  	String query="DELETE FROM CustomerList WHERE customerId = " +customerId;
	                System.out.println(query);
		  	try{
		 		Statement stmt = connection.createStatement();
		 		stmt.setQueryTimeout(5);
		 	  	rc = stmt.executeUpdate(query);
		 	  	stmt.close();
	  		}
	   	        catch(Exception ex){
		 	  	System.out.println("Delete exception in Customer db: "+ex);
	   	        }
			return(rc);
		}

	 public int insertCustomer(Customer customer) throws Exception
	    {   
	        
		 
	       int rc = -1;
		   String query="INSERT INTO CustomerList(customerId, name, surname, address, email, phone, company)  VALUES "
				+ "("+customer.getCustomerId()+",'"+customer.getCustomerName()+"','"+customer.getCustomerSurname()+"','"+customer.getCustomerAddress()+"','"+customer.getCustomerEmail()+"','"+customer.getCustomerPhone()+"','"+customer.getCustomerCompany()+"')";
	                     

	       System.out.println("insert : " + query);
	      try{ 
	          Statement stmt = connection.createStatement();
	          stmt.setQueryTimeout(5);
	     	  rc = stmt.executeUpdate(query);
	          stmt.close();
	         }
	       catch(SQLException ex){
	          System.out.println("Customer insert Error");
	          throw new Exception ("Customer is not inserted. Please contact administration.");
	          
	       }
	     
	       return(rc);
	       
	    }

	 public Customer findCustomer(int customerId)
	    {   String wClause = "  customerId = " + customerId;
	        return singleWhere(wClause);
	    }
	 public Customer findByName(String name) 
	 	{
			return singleWhere("name = '" + name + "'");
		}

	 
	 private Customer singleWhere(String wClause)
		{
			Customer customer = new Customer();
			
			ResultSet results;
			String query = buildQuery(wClause);
			
			
			try{
				Statement stmt = connection.createStatement();
				stmt.setQueryTimeout(5);
				results = stmt.executeQuery(query);
				
				if(results.next())
					customer = buildCustomer(results);
				
				stmt.close();
			}
			catch(Exception ex){}
			
			return customer;
		}

	
	 
}
